﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;
namespace DVSProject.Lib
{
    public class AdminCustomerRepo
    {
        private const string key = "yaservnet";
        public const string m_msg_head = "AdminCustomer";
        public Crypto _crypto = new Crypto();
        public IEnumerable<AdminCustomerModels> GetAdminCustomer()
        {
            List<AdminCustomerModels> AdminCustomers = new List<AdminCustomerModels>();
            string SQLCommand = @" select TBAdminCustomer.*,TBLocationSet.LocationSetCode +'-'+TBLocationSet.LocSetNameTh AS LocSet_Name 
                                  FROM TBAdminCustomer LEFT JOIN TBLocationSet 
                                     ON TBAdminCustomer.LocationSetCode = TBLocationSet.LocationSetCode
                                  WHERE  TBAdminCustomer.Status <> 'Delete'
                                  ORDER BY AdminId";
            using (var db = new DBHelper())
            {
                try
                {
                   
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        AdminCustomerModels _AdminCustomerModels = new AdminCustomerModels();

                        _AdminCustomerModels.AdminId = (string)reader["AdminId"];
                        _AdminCustomerModels.LocationSetCode = (string)reader["LocationSetCode"];
                        _AdminCustomerModels.LocSet_Name = (string)reader["LocSet_Name"];
                        _AdminCustomerModels.UserName = (string)reader["UserName"];
                        _AdminCustomerModels.Password = (string)reader["Password"];//Crypto.DecryptMessage(key, (string)reader["Password"]);
                        _AdminCustomerModels.CodeConfirm = Crypto.DecryptMessage(key, (string)reader["CodeConfirm"]);//(string)reader["CodeConfirm"]; 
                        _AdminCustomerModels.NameTh = (string)reader["NameTh"];
                        _AdminCustomerModels.NameEng = (string)reader["NameEng"];
                        _AdminCustomerModels.Status = (string)reader["Status"];
                        _AdminCustomerModels.CreateBy = (string)reader["CreateBy"];
                        _AdminCustomerModels.CreateOn = (DateTime)reader["CreateOn"];
                        _AdminCustomerModels.UpdateBy = (string)reader["UpdateBy"];
                        _AdminCustomerModels.UpdateOn = (DateTime)reader["UpdateOn"];

                        AdminCustomers.Add(_AdminCustomerModels);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return AdminCustomers.ToList();
        }
        public IEnumerable<AdminCustomerModels> GetAdminCustomer(string FSR_NO)
        {
            List<AdminCustomerModels> AdminCustomers = new List<AdminCustomerModels>();
            string SQLCommand = @" declare @LocationSetCode varchar (20)
                                    select @LocationSetCode = locationSetCode 
                                    from tbvisithd where FSR_NO=@FSR_NO

                                    select TBAdminCustomer.*,TBLocationSet.LocationSetCode +'-'+TBLocationSet.LocSetNameTh AS LocSet_Name 
                                  FROM TBAdminCustomer LEFT JOIN TBLocationSet 
                                     ON TBAdminCustomer.LocationSetCode = TBLocationSet.LocationSetCode
                                  WHERE TBAdminCustomer.locationSetCode=@LocationSetCode And TBAdminCustomer.Status <> 'Delete'
                                  ORDER BY AdminId";
            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@FSR_NO", FSR_NO);
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        AdminCustomerModels _AdminCustomerModels = new AdminCustomerModels();
                        
                        _AdminCustomerModels.AdminId = (string)reader["AdminId"];
                        _AdminCustomerModels.LocationSetCode = (string)reader["LocationSetCode"];
                        _AdminCustomerModels.LocSet_Name = (string)reader["LocSet_Name"];
                        _AdminCustomerModels.UserName =  (string)reader["UserName"];
                        _AdminCustomerModels.Password = (string)reader["Password"];//Crypto.DecryptMessage(key, (string)reader["Password"]);
                        _AdminCustomerModels.CodeConfirm = Crypto.DecryptMessage(key, (string)reader["CodeConfirm"]);//(string)reader["CodeConfirm"]; 
                        _AdminCustomerModels.NameTh = (string)reader["NameTh"];
                        _AdminCustomerModels.NameEng = (string)reader["NameEng"];
                        _AdminCustomerModels.Status = (string)reader["Status"];
                        _AdminCustomerModels.CreateBy = (string)reader["CreateBy"];
                        _AdminCustomerModels.CreateOn = (DateTime)reader["CreateOn"];
                        _AdminCustomerModels.UpdateBy = (string)reader["UpdateBy"];
                        _AdminCustomerModels.UpdateOn = (DateTime)reader["UpdateOn"];

                        AdminCustomers.Add(_AdminCustomerModels);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return AdminCustomers.ToList();
        }

         public AdminCustomerModels GetAdminCustomerId(string id)
        {
            AdminCustomerModels _AdminCustomerModels = new AdminCustomerModels();

            string SQLCommand = @"select TBAdminCustomer.*,TBLocationSet.LocationSetCode +'-'+TBLocationSet.LocSetNameTh AS LocSet_Name 
                                  FROM TBAdminCustomer LEFT JOIN TBLocationSet 
                                     ON TBAdminCustomer.LocationSetCode = TBLocationSet.LocationSetCode
                                  where AdminId=@AdminId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@AdminId", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        _AdminCustomerModels.AdminId = (string)reader["AdminId"];
                        _AdminCustomerModels.LocationSetCode = (string)reader["LocationSetCode"];
                        _AdminCustomerModels.LocSet_Name =(string)reader["LocSet_Name"];
                        _AdminCustomerModels.UserName = (string)reader["UserName"];
                        _AdminCustomerModels.Password = Crypto.DecryptMessage(key,(string)reader["Password"]);
                        _AdminCustomerModels.CodeConfirm = Crypto.DecryptMessage(key,(string)reader["CodeConfirm"]);
                        _AdminCustomerModels.NameTh = (string)reader["NameTh"];
                        _AdminCustomerModels.NameEng = (string)reader["NameEng"];
                        _AdminCustomerModels.Status = (string)reader["Status"];
                        _AdminCustomerModels.CreateBy = (string)reader["CreateBy"];
                        _AdminCustomerModels.CreateOn = (DateTime)reader["CreateOn"];
                        _AdminCustomerModels.UpdateBy = (string)reader["UpdateBy"];
                        _AdminCustomerModels.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return _AdminCustomerModels;
        }

        public bool Create(AdminCustomerModels AdminCustomer)
        {
            bool result = true;
            string SQLCommand = @"DECLARE @STRYEAR	varchar(4)
	                            DECLARE @STRDOCNO_1	varchar(7)
	                            DECLARE @LASTNO		smallint
	                            SET @STRYEAR = RIGHT(convert(varchar(4),YEAR(getdate())) ,2)
	                            SET @LASTNO = (SELECT  isnull(max(right(isnull(AdminId ,0),4)),0)+1 FROM TBAdminCustomer ) 
	                            SET @STRDOCNO_1='A'+@STRYEAR + STUFF('0000', len('0000')-(len(@LASTNO)-1), len(@LASTNO),@LASTNO)
                                INSERT into TBAdminCustomer
                                values (@STRDOCNO_1,@LocationSetCode,@UserName,@Password,@CodeConfirm,@NameTh,@NameEng,@Status,@UserId,
                                GETDATE(),@UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@LocationSetCode", AdminCustomer.LocationSetCode);
                    db.AddParameter("@UserName", AdminCustomer.UserName);
                    db.AddParameter("@Password", Crypto.EncryptMessage(key, AdminCustomer.Password));
                    db.AddParameter("@CodeConfirm", Crypto.EncryptMessage(key,AdminCustomer.CodeConfirm));
                    db.AddParameter("@NameTh", AdminCustomer.NameTh);
                    db.AddParameter("@NameEng", AdminCustomer.NameEng);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID",AdminCustomer.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(AdminCustomerModels AdminCustomer)
        {
            bool result = true;
            string SQLCommand = @"Update TBAdminCustomer 
                                    Set LocationSetCode =@LocationSetCode,
                                        UserName=@UserName,
                                        Password=@Password,
                                        CodeConfirm=@CodeConfirm,
                                        NameTh=@NameTh,
                                        NameEng=@NameEng,
                                        UpdateBy=@UserId,UpdateOn=getdate()
                                    where AdminId=@AdminId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@AdminId", AdminCustomer.AdminId);
                    db.AddParameter("@LocationSetCode", AdminCustomer.LocationSetCode);
                    db.AddParameter("@UserName", AdminCustomer.UserName);
                    db.AddParameter("@Password",  Crypto.EncryptMessage(key,AdminCustomer.Password));
                    db.AddParameter("@CodeConfirm", Crypto.EncryptMessage(key, AdminCustomer.CodeConfirm));
                    db.AddParameter("@NameTh", AdminCustomer.NameTh);
                    db.AddParameter("@NameEng", AdminCustomer.NameEng);
                    db.AddParameter("@UserID", AdminCustomer.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(AdminCustomerModels AdminCustomer)
        {
            bool result = true;
            string SQLCommand = @"Update TBAdminCustomer
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where AdminId=@AdminId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@AdminId", AdminCustomer.AdminId);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", AdminCustomer.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}